use warnings;
use strict;

#use Win32::ODBC;
use DBI;
use DBD::ODBC;
use Audio::Beep;
use IO::Handle;  # For autoflush
use Carp;

sub import_data
{
  my $dbh_local = shift;
  my $dbh_remote = shift;
  my $table_name = shift;

  print "Doing import for table $table_name...\n";

  my $sql_column_names = "select column_name,data_type from information_schema.columns where table_name = '$table_name'";
  my $column_names_handle = $dbh_local->prepare($sql_column_names) or die "failed to get column names for $table_name\n";
  $column_names_handle->execute();

  my $column_name;
  my $data_type;
  my %column_name_type_list;
  $column_names_handle->bind_columns(\$column_name,\$data_type);
  while ($column_names_handle->fetch()) {
	  $column_name_type_list{$column_name} = $data_type;
  }
  $column_names_handle->finish();

  my $joined_column_names_str;
  for my $column_name (keys %column_name_type_list) {
	  $joined_column_names_str .= $column_name . ",";
  }
  $joined_column_names_str =~ s/,$//;	#remove the last ,

  my $sql_select = "SELECT $joined_column_names_str FROM $table_name";
  my $select_handle = $dbh_local->prepare($sql_select) or die "Failed to prepare($sql_select)\n";
  $select_handle->execute();

  my $bind_column1;
  my $bind_column2;
  my $bind_column3;
  my $bind_column4;
  my $bind_column5;
  my $bind_column6;
  my $bind_column7;
  my $bind_column8;
  my $bind_column9;
  my $bind_column10;
  my $bind_column11;
  my $bind_column12;
  my $bind_column13;
  my $bind_column14;
  my $bind_column15;

  my @all_columns = ($bind_column1,$bind_column2,$bind_column3,$bind_column4,$bind_column5,
	  $bind_column6,$bind_column7,$bind_column8,$bind_column9,$bind_column10,
	  $bind_column11,$bind_column12,$bind_column13,$bind_column14,$bind_column15);

  my @bind_columns = @all_columns[0..(scalar(keys %column_name_type_list) - 1)];
 
  $select_handle->bind_columns(\(@bind_columns)) or die "failed to bind columns, $?";

  while($select_handle->fetch())
	{
	  my @column_value_list;
	  for (my $i=0; $i<@bind_columns;$i++) {
		  my $column_value = $bind_columns[$i];
		  #if ( (values %column_name_type_list)[$i] !~ /money/i) {
			  $column_value = "'" . $column_value . "'";
		  #}
		  push(@column_value_list,$column_value);
	  }
	  my $bind_columns_values_str = join(',',@column_value_list);

	  my $sql_insert = "set identity_insert $table_name on; INSERT INTO $table_name($joined_column_names_str) values($bind_columns_values_str); set identity_insert $table_name off";
	  print "$sql_insert\n";
	  $dbh_remote->do($sql_insert) or die "failed to insert!!!";
	}
	$select_handle->finish();
}
sub clear_table
{
	my $dbh = shift;
	my $table_name = shift;

	$dbh->do("delete from $table_name");
}

sub connect_server
{
  my $database = shift;
  my $host = shift;
  my $uid = shift;
  my $pwd = shift;

  my $conn_string = "driver={SQL Server};Server=$host;Database=$database;UID=$uid;PWD=$pwd";
  my $dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or return undef;

  return $dbh;
}
sub main()
{
  my @local_server  = ('fanchuan0077','vip9.sudu.hi.cn','fanchuan0077','fanchuan0077');
  my @remote_server = ('fanchuan0077','112.121.175.102','fanchuan0077','db135246');

  my $dbh_local = connect_server(@local_server) or die "Failed to connect local server!!!\n";
  print "Connected to local server successfully!\n";

  my $dbh_remote = connect_server(@remote_server) or die "Failed to connect remote server!!!\n";
  print "Connected to remote server successfully!\n";

  my @tables = ('owner', 'dish','delivery','district','address','restaurant','category','categorydish','serveddistrict','ingredient','dishingredient');
  # clear all tables before reconstruction.
  for (my $i=scalar(@tables)-1; $i>=0; $i--) {
	  clear_table($dbh_remote,$tables[$i]);
  }

  # reconstruction
  foreach my $table (@tables) {
	  import_data($dbh_local,$dbh_remote ,$table);
  }

  $dbh_remote->disconnect();
  $dbh_local->disconnect();

  print "end of watch.\n";
}

main();